using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Data;
using System.IO;
using System.Text;
using System.Diagnostics;
using System.Security.Cryptography;
using System.Reflection;
using System.Net;
using System.Net.Sockets;
using zlib;
using System.Runtime.InteropServices;
using NETCommon.Files;

//[assembly: System.Security.SecurityTransparent] 
namespace NETCommon.Data
{
    public class NDatabase
    {

        //Decalre variable
        string strConnectionString = "";
        
        MySqlDataAdapter MySqlAdp;
        string strServerName = "";
        string strServerDesc = "";
        public bool isReload = false;
        public bool isActive = true;

        //Pooling setup
        private bool isPooling=false;
        private uint MaximumPoolSize=5;
        private uint MinimumPoolSize=1;
        private uint ConnectionLifeTime=60;
        MySqlCommand MySqlComm;

        private int nHostId = 0;

        NConfig tisconfig=null;
        //SConn property
        public string sConn
        {
            get { return strConnectionString; }
        }

        public string sServerName
        {
            get { return strServerName; }
        }

        public string sServerDesc
        {
            get { return strServerDesc; }
        }


        //Get defaul value
        public NDatabase()
        {
            isReload = true;
            tisconfig = NConfig.Instance;
            this.nHostId = 0;
            MySqlAdp = new MySqlDataAdapter();
            strConnectionString = fncGetConnectionString();
          
        }
        public void SetupConnectionPool(uint _MaximumPoolSize,uint _MinimumPoolSize,uint _ConnectionLifeTime) 
        {
            isPooling = true;
            MaximumPoolSize = _MaximumPoolSize;
            MinimumPoolSize = _MinimumPoolSize;
            ConnectionLifeTime = _ConnectionLifeTime;
            strConnectionString = fncGetConnectionString();
        }
        public void SetupConnectionPool()
        {
            isPooling = true;
            strConnectionString = fncGetConnectionString();
        }
        public NDatabase(int nHostId)
        {
            isReload = true;
            tisconfig = NConfig.Instance;
            this.nHostId = nHostId;
            MySqlAdp = new MySqlDataAdapter();
            strConnectionString = fncGetConnectionString();
        }


        /// <summary>
        ///  Executing query (SELECT) and return dataset
        /// </summary>
        /// <param name="sSQL">SQL Query statement</param>
        /// <param name="sTableName">Name of table which you want</param>
        /// <returns>Return DataSet for the query</returns>
        public DataSet fcnExecQuery(string sSQL, string sTableName)
        {
            DataSet ds = new DataSet();

            try
            {
                if (!isActive)
                {
                    return null;
                }
                if (isReload)
                {
                    strConnectionString = fncGetConnectionString();                  
                    isReload = false;
                }

                MySqlConnection MySqlConn = new MySqlConnection(sConn);
                
                MySqlConn.Open();
                MySqlComm = new MySqlCommand(sSQL, MySqlConn);
                MySqlAdp.SelectCommand = MySqlComm;
                MySqlAdp.Fill(ds, sTableName);
                MySqlConn.Close();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        ///  Executing query to Update or Insert data into Database
        /// </summary>
        /// <param name="sSQL">SQL Query statement</param>
        /// <returns>Return TRUE if successfully, FALSE if not</returns>
        public bool fcnExecNonQuery(string sSQL)
        {

            bool bReturn = false;

            try
            {
                if (!isActive)
                {
                    return false;
                }

                if (isReload)
                {
                    
                    strConnectionString = fncGetConnectionString();
                    isReload = false;
                }

                MySqlConnection MySqlConn = new MySqlConnection(sConn);
                MySqlConn.Open();
                MySqlComm = new MySqlCommand(sSQL, MySqlConn);
                MySqlComm.ExecuteNonQuery();
                MySqlConn.Close();

                bReturn = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return bReturn;
        }
        public bool fcnExecNonQuery(string strConnection, string sSQL)
        {

            bool bReturn = false;

            try
            {

                strConnectionString = strConnection;

                MySqlConnection MySqlConn = new MySqlConnection(sConn);
                MySqlConn.Open();
                MySqlComm = new MySqlCommand(sSQL, MySqlConn);
                MySqlComm.ExecuteNonQuery();
                MySqlConn.Close();

                bReturn = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return bReturn;
        }

        /// <summary>
        /// Execute return DataAdapter.
        /// </summary>
        /// <param name="sSQL"></param>
        /// <returns>return MySqlDataAdapter to update, delete, add from grid</returns>
        public MySqlDataAdapter fcnAdapter(string sSQL)
        {

            try
            {

                if (!isActive)
                {
                    return null; ;
                }

                if (isReload)
                {
                    strConnectionString = fncGetConnectionString();
                }

                MySqlConnection MySqlConn = new MySqlConnection(sConn);
                MySqlAdp = new MySqlDataAdapter(sSQL, MySqlConn);
                return MySqlAdp;

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// Get connection string 
        /// </summary>
        /// <returns>return connection string</returns>
        private string fncGetConnectionString()
        {

            string sReturn = "";           
            try
            {   
                if (File.Exists(NConfigDefine.g_configfile))
                {
                    sReturn = "";
                    MySql.Data.MySqlClient.MySqlConnectionStringBuilder myConStr = new MySqlConnectionStringBuilder();
                    myConStr.Server=tisconfig.ServerAddress(this.nHostId);
                    myConStr.Database= tisconfig.Database(this.nHostId);
                    myConStr.UserID=tisconfig.User(this.nHostId);
                    myConStr.Password=tisconfig.Password(this.nHostId);
                    myConStr.CharacterSet="utf8";
                    myConStr.ConnectionTimeout=10;

                    if (isPooling)
                    {
                        myConStr.Pooling = true;
                        myConStr.MaximumPoolSize = MaximumPoolSize;
                        myConStr.MinimumPoolSize = MinimumPoolSize;
                        myConStr.ConnectionLifeTime = ConnectionLifeTime;
                        myConStr.ConnectionReset = false;
                    }
                    else
                    {
                        myConStr.Pooling = false;
                    }

                    sReturn = myConStr.ConnectionString;
                    strServerName = tisconfig.ServerName(this.nHostId);
                    strServerDesc = tisconfig.ServerName(this.nHostId);
                }            
                return sReturn;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string GetDatabaseName()
        {
            string sDbName;
            sDbName = " " + tisconfig.Database(this.nHostId) + ";";
            return sDbName;
        }

        /// <summary>
        /// Check SQL connection if not connected
        /// </summary>
        /// <returns>True is connected, False is not connect</returns>
        public bool fncCheckConnection()
        {
            try
            {
                strConnectionString = fncGetConnectionString();
                MySqlConnection MySqlConn = new MySqlConnection(strConnectionString);
                MySqlConn.Open();
                MySqlConn.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Check SQL connection if not connected
        /// </summary>
        /// <param name="Servername">Name of MySQL Server</param>
        /// <param name="DatabaseName">Name of MySQL database</param>
        /// <param name="UserName">Name of user login</param>
        /// <param name="Password">Password of user</param>
        /// <returns></returns>
        public static bool fncCheckConnection(string Servername, string DatabaseName, string UserName, string Password)
        {
            try
            {
                string strConnString;
                strConnString = "Data Source=" + Servername +
                                   ";Initial Catalog=" + DatabaseName +
                                   ";User ID=" + UserName +
                                   ";Password=" + Password;

                MySqlConnection MySqlConn = new MySqlConnection(strConnString);
                MySqlConn.Open();
                MySqlConn.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static string fncGetConnectionString(string Servername, string DatabaseName, string UserName, string Password)
        {
            String sReturn = "";
            MySql.Data.MySqlClient.MySqlConnectionStringBuilder myConStr = new MySqlConnectionStringBuilder();
            myConStr.Server = Servername;
            myConStr.Database = DatabaseName;
            myConStr.UserID = UserName;
            myConStr.Password = Password;
            myConStr.CharacterSet = "utf8";
            myConStr.ConnectionTimeout = 10;
            myConStr.Pooling = false;


            sReturn = myConStr.ConnectionString;
            return sReturn;
        }

        public static bool fncCheckConnection(string strConnection)
        {
            try
            {
                using (MySql.Data.MySqlClient.MySqlConnection connTest = new MySql.Data.MySqlClient.MySqlConnection(strConnection))
                {
                    connTest.Open();
                    connTest.Close();
                }
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

#region "ADVANCED FUNCTIONS"

        public static string GetConfig()
        {

            DataSet ds = new DataSet();
            string sPathName;
            DataRow dr;

            string sReturn = "";
            try
            {
                sPathName = AppDomain.CurrentDomain.BaseDirectory.ToString();
                if (File.Exists(sPathName + "\\DBInfo.xml"))
                {
                    //AppManager myManager = new AppManager(AppDomain.CurrentDomain.BaseDirectory + "\\" + "DBConfig.xml");
                    ////Show information to form
                    //sReturn = "Data Source=" + myManager.getAppSettingValue("ServerName") +
                    //           ";Initial Catalog=" + myManager.getAppSettingValue("DatabaseName") +
                    //           ";User ID=" + myManager.getAppSettingValue("UserName") +
                    //           ";Password=" + myManager.getAppSettingValue("Password") +
                    //            ";charset=utf8 ;Connection Timeout=30;Pooling=false;";
                    //";charset=utf8 ;Connection Timeout=30;Pooling=true;Min Pool Size=0;Max Pool Size=10;";

                    //Read data from dataset
                    ds.ReadXml(sPathName + "\\DBInfo.xml");
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dr = ds.Tables[0].Rows[0];
                        sReturn = dr["ServerName"].ToString() + "|" + dr["DatabaseName"].ToString() +
                               "|" + dr["UserName"].ToString() +
                               "|" + dr["Password"].ToString();
                    }
                }
                return sReturn;
            }
            catch (Exception ex)
            {
                throw ex;

            }
        }
        public void AdapterSetup(MySqlCommand updateCmd, MySqlCommand insertCmd, MySqlCommand deleteCmd)
        {
            MySqlAdp.UpdateCommand = updateCmd;
            MySqlAdp.InsertCommand = insertCmd;
            MySqlAdp.DeleteCommand = deleteCmd;
        }
        public MySqlConnection GetSqlConnection()
        {
                MySqlConnection MySqlConn = new MySqlConnection(sConn);
                return MySqlConn;
        }

        /// <summary>
        /// Check SQL connection if not connected
        /// </summary>
        /// <returns>True is connected, False is not connect</returns>

        public bool SaveData(DataTable dt, string strTableName)
        {
            string strConnection = fncGetConnectionString();
            string strSQLQuery = "SELECT * FROM " + strTableName + " WHERE 1=0 ";
            DataTable dtChanges = null;
            bool boolBegin = false;
            MySqlTransaction trans = null;

            try
            {

                MySqlConnection conn;
                MySqlDataAdapter adap;
                MySqlCommandBuilder comBuild;

                conn = new MySqlConnection(strConnection);
                conn.Open();

                boolBegin = true;
                adap = new MySqlDataAdapter(strSQLQuery, conn);
                //adap.MissingSchemaAction = MissingSchemaAction.AddWithKey
                
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                //trans = conn.BeginTransaction();
                adap.SelectCommand.Transaction = trans;

                //boolBegin = true;
                //adap = new MySqlDataAdapter(strSQLQuery, conn);
                ////adap.MissingSchemaAction = MissingSchemaAction.AddWithKey

                //trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                ////trans = conn.BeginTransaction()
                //adap.SelectCommand.Transaction = trans;

                comBuild = new MySqlCommandBuilder(adap);
                comBuild.ConflictOption = ConflictOption.OverwriteChanges;
                
                dtChanges = dt.GetChanges();
                if (dtChanges != null)
                {
                    adap.Update(dtChanges);
                }

                trans.Commit();

                boolBegin = false;

                dt.AcceptChanges();
                conn.Close();
            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }

                throw (new Exception(ex.ToString(), ex));
            }

            return true;
        }
        public bool SaveData(string strSQLAction, MySqlParameter[] oleParams)
        {

            string strConnection = fncGetConnectionString();
            bool boolBegin = false;
            MySqlTransaction trans = null;
            int i;
            int n;

            try
            {

                MySqlConnection conn;
                MySqlCommand cmdCommand;

                n = oleParams.Length;

                conn = new MySqlConnection(strConnection);
                conn.Open();
                //Initialize transaction
                //trans = conn.BeginTransaction(IsolationLevel.RepeatableRead)
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                boolBegin = true;
                //Init Command
                cmdCommand = new MySqlCommand(strSQLAction, conn, trans);
                for (i = 1; i <= n; i++)
                {
                    cmdCommand.Parameters.Add(oleParams[i - 1]);
                }

                cmdCommand.ExecuteNonQuery();

                trans.Commit();

                boolBegin = false;

                conn.Close();

            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }
                throw (new Exception(ex.ToString(), ex));
            }

            return true;
        }

        /*
        *Develop from middle of DEC2009 
        */
        public DataTable GetTableSchema(string strTableName)
        {
            DataTable dtGet = null;
            try
            {
                string strSQLQuery = "SELECT * FROM " + strTableName;
                MySqlConnection MySqlConn = new MySqlConnection(sConn);
                MySqlCommand myCommand = new MySqlCommand(strSQLQuery, MySqlConn);
                
                MySqlConn.Open();
                MySqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                //Get schema
                dtGet = myReader.GetSchemaTable();//This table has columns: ColumnName, DataType field

                //Close all connection
                MySqlConn.Close();
                myReader.Close();
            }
            catch (Exception)
            {
                return null;
            }

            return dtGet;
        }

        /// <summary>
        /// Get Scalar data
        /// </summary>
        /// <param name="strSQLQuery"> This param contains a select value, ex: Select TagID from eventtag Where eventname = ...  </param>
        /// <returns></returns>
        
        public static Object GetScalar(string strSQLQuery, string strConnection)
        {
            //Return a Scalar value
            MySqlConnection conn;
            MySqlCommand cmd;

            Object obj;

            try
            {
                conn = new MySqlConnection(strConnection);
                conn.Open();

                cmd = new MySqlCommand(strSQLQuery, conn);
                obj = cmd.ExecuteScalar();

                conn.Close();
            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }

            return obj;
        }

        public static Object GetScalar(string strSQLQuery, string strConnection, MySqlParameter[] arrParamName)
        {
            //Return a Scalar value
            MySqlConnection conn;
            MySqlCommand cmd;

            //arrParamName[i] = ?Name
            //Ex, strSQLQuery = "SELECT * FROM user WHERE userid = @userin;"
            Object obj;

            try
            {
                conn = new MySqlConnection(strConnection);
                conn.Open();

                cmd = new MySqlCommand(strSQLQuery, conn);

                foreach (MySqlParameter paramItem in arrParamName)
                {
                    cmd.Parameters.Add(paramItem);
                }

                obj = cmd.ExecuteScalar();

                conn.Close();
            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }

            return obj;
        }

        /// <summary>
        /// Get a DataTable by the strSQLQuery query 
        /// </summary>
        /// <param name="strSQLQuery">SQL Select</param>
        /// <param name="strConnection">Connection string to a database</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string strSQLQuery, string strConnection, string strTableName)
        {
            //Return a DataTable by a select Statement
            DataTable dtTemp;
            dtTemp = null;
            MySqlConnection conn;
            MySqlDataAdapter adap;

            try
            {
                conn = new MySqlConnection(strConnection);
                conn.Open();

                adap = new MySqlDataAdapter(strSQLQuery, conn);
                dtTemp = new DataTable();
                adap.Fill(dtTemp);
                dtTemp.TableName = strTableName;

                conn.Close();
            }
            catch (Exception)
            {
                return null;
            }

            return dtTemp;
        }

        public static DataTable GetDataTable(string strSQLQuery, string strConnection, string strTableName, MySqlParameter[] arrParamName)
        {
            //Return a DataTable by a select Statement
            DataTable dtTemp;
            dtTemp = null;
            MySqlConnection conn;
            MySqlDataAdapter adap;
            MySqlCommand cmd;

            try
            {
                conn = new MySqlConnection(strConnection);
                conn.Open();

                cmd = new MySqlCommand(strSQLQuery, conn);
                foreach (MySqlParameter paramItem in arrParamName)
                {
                    cmd.Parameters.Add(paramItem);
                }

                //adap = new MySqlDataAdapter(strSQLQuery, conn);
                adap = new MySqlDataAdapter(cmd);

                dtTemp = new DataTable();
                adap.Fill(dtTemp);
                dtTemp.TableName = strTableName;

                conn.Close();
            }
            catch (Exception)
            {
                return null;
            }

            return dtTemp;
        }

        public static bool SaveData(DataTable dt, string strTableName, string strConnection)
        {
            string strSQLQuery = "SELECT * FROM " + strTableName + " WHERE 1=0 ";
            DataTable dtChanges = null;
            bool boolBegin = false;
            MySqlTransaction trans = null;

            try
            {

                MySqlConnection conn;
                MySqlDataAdapter adap;

                conn = new MySqlConnection(strConnection);
                conn.Open();

                boolBegin = true;
                adap = new MySqlDataAdapter(strSQLQuery, conn);
                //adap.MissingSchemaAction = MissingSchemaAction.AddWithKey

                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                //trans = conn.BeginTransaction()
                adap.SelectCommand.Transaction = trans;

                dtChanges = dt.GetChanges();
                if (dtChanges != null)
                {
                    adap.Update(dtChanges);
                }

                trans.Commit();

                boolBegin = false;

                dt.AcceptChanges();
                conn.Close();
            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }

                throw (new Exception(ex.ToString(), ex));
            }

            return true;
        }

        public static bool SaveData(DataSet ds, string[] strTableName, string strConnection)
        {
            string strSQLQuery = "SELECT * FROM {0} WHERE 1=0 ";

            bool boolBegin = false;
            MySqlTransaction trans = null;
            int i;
            int n;

            //Note the total table in ds Dataset is equal the member in strTableName array

            try
            {

                MySqlConnection conn;
                MySqlDataAdapter adap;

                n = ds.Tables.Count;

                conn = new MySqlConnection(strConnection);
                conn.Open();


                boolBegin = true;

                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                //Because of DataRelation, so, We must save data by some sequence
                //Save INSERT and UPDATE all tables by sequence
                for (i = 0; i <= n - 1; i++)
                {
                    adap = new MySqlDataAdapter(string.Format(strSQLQuery, strTableName[i]), conn);
                    //adap.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    //adap.FillSchema(ObjDataTable, SchemaType.Source)

                    //trans = conn.BeginTransaction()
                    adap.SelectCommand.Transaction = trans;

                    DataTable dtChanges = null;
                    dtChanges = ds.Tables[i].GetChanges(DataRowState.Added | DataRowState.Modified);
                    if (dtChanges != null)
                    {
                        adap.Update(dtChanges);
                    }
                }

                //Save DELETE all tables by sequence
                for (i = n - 1; i >= 0; i--)
                {
                    adap = new MySqlDataAdapter(string.Format(strSQLQuery, strTableName[i]), conn);
                    //adap.MissingSchemaAction = MissingSchemaAction.AddWithKey

                    //trans = conn.BeginTransaction()
                    adap.SelectCommand.Transaction = trans;

                    DataTable dtChanges = null;
                    dtChanges = ds.Tables[i].GetChanges(DataRowState.Deleted);
                    if (dtChanges != null)
                    {
                        adap.Update(dtChanges);
                    }
                }

                trans.Commit();
                boolBegin = false;
                ds.AcceptChanges();

                conn.Close();
            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }

                throw (new Exception(ex.ToString(), ex));
            }

            return true;
        }

        public static bool SaveData(DataTable[] strArrDataTable, string[] strTableName, string strConnection)
        {
            string strSQLQuery = "SELECT * FROM {0} WHERE 1=0 ";

            bool boolBegin = false;
            MySqlTransaction trans = null;
            int i;
            int n;

            //Note the total table in ds Dataset is equal the member in strTableName array

            try
            {

                MySqlConnection conn;
                MySqlDataAdapter adap;
                MySqlCommandBuilder comBuild;

                n = strTableName.Length;

                conn = new MySqlConnection(strConnection);
                conn.Open();

                boolBegin = true;

                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                //Because of DataRelation, so, We must save data by some sequence
                //Save INSERT and UPDATE all tables by sequence
                for (i = 0; i <= n - 1; i++)
                {
                    adap = new MySqlDataAdapter(string.Format(strSQLQuery, strTableName[i]), conn);
                    //adap.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    //adap.FillSchema(ObjDataTable, SchemaType.Source)

                    //trans = conn.BeginTransaction()
                    adap.SelectCommand.Transaction = trans;

                    comBuild = new MySqlCommandBuilder(adap);
                    comBuild.ConflictOption = ConflictOption.OverwriteChanges;


                    //====BEGIN CODE: Some case abnormal DataAdapter cannot automatically generate INSERT, UPDATE , DELETE command, so, we must write more
                    //adap.UpdateCommand = comBuild.GetUpdateCommand();
                    //====END CODE: Some case abnormal DataAdapter cannot automatically generate INSERT, UPDATE , DELETE command, so, we must write more


                    DataTable dtChanges = null;
                    dtChanges = strArrDataTable[i].GetChanges(DataRowState.Added | DataRowState.Modified);
                    if (dtChanges != null)
                    {
                        adap.Update(dtChanges);
                    }
                }

                //Save DELETE all tables by sequence
                for (i = n - 1; i >= 0; i--)
                {
                    adap = new MySqlDataAdapter(string.Format(strSQLQuery, strTableName[i]), conn);
                    //adap.MissingSchemaAction = MissingSchemaAction.AddWithKey

                    //trans = conn.BeginTransaction()
                    adap.SelectCommand.Transaction = trans;

                    comBuild = new MySqlCommandBuilder(adap);

                    DataTable dtChanges = null;
                    dtChanges = strArrDataTable[i].GetChanges(DataRowState.Deleted);
                    if (dtChanges != null)
                    {
                        adap.Update(dtChanges);
                    }
                }

                trans.Commit();
                boolBegin = false;

                //Accept changes all table
                for (i = 0; i <= n - 1; i++)
                {
                    strArrDataTable[i].AcceptChanges();
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }

                throw (new Exception(ex.ToString(), ex));
            }

            return true;
        }

        public static bool SaveData(string strSQLAction, string strConnection)
        {

            bool boolBegin = false;
            MySqlTransaction trans = null;

            try
            {

                MySqlConnection conn;
                MySqlCommand cmdCommand;


                conn = new MySqlConnection(strConnection);
                conn.Open();
                //Initialize transaction
                //trans = conn.BeginTransaction(IsolationLevel.RepeatableRead)
                //trans = conn.BeginTransaction()

                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                //Init Command
                cmdCommand = new MySqlCommand(strSQLAction, conn, trans);

                boolBegin = true;

                cmdCommand.ExecuteNonQuery();

                trans.Commit();

                boolBegin = false;

                conn.Close();

            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }
                throw (new Exception(ex.ToString(), ex));
            }

            return true;
        }

        public static bool SaveData(string strSQLAction, MySqlParameter[] oleParams, string strConnection)
        {

            bool boolBegin = false;
            MySqlTransaction trans = null;
            int i;
            int n;

            try
            {

                MySqlConnection conn;
                MySqlCommand cmdCommand;

                n = oleParams.Length;

                conn = new MySqlConnection(strConnection);
                conn.Open();
                //Initialize transaction
                //trans = conn.BeginTransaction(IsolationLevel.RepeatableRead)
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                boolBegin = true;
                //Init Command
                cmdCommand = new MySqlCommand(strSQLAction, conn, trans);
                for (i = 1; i <= n; i++)
                {
                    cmdCommand.Parameters.Add(oleParams[i - 1]);
                }

                cmdCommand.ExecuteNonQuery();

                trans.Commit();

                boolBegin = false;

                conn.Close();

            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }
                throw (new Exception(ex.ToString(), ex));
            }

            return true;
        }

        public static DataTable GetTableSchema(string strTableName, string strConnectionString)
        {
            DataTable dtGet = null;
            MySqlConnection MyConn;
            try
            {
                string strSQLQuery = "SELECT * FROM " + strTableName;
                MyConn = new MySqlConnection(strConnectionString);

                MySqlCommand myCommand = new MySqlCommand(strSQLQuery, MyConn);
                if (MyConn.State != ConnectionState.Open)
                    MyConn.Open();

                MySqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                //Get schema
                dtGet = myReader.GetSchemaTable();//This table has columns: ColumnName, DataType field

                //Close all connection
                MyConn.Close();
                myReader.Close();
            }
            catch (Exception)
            {
                return null;
            }

            return dtGet;
        }

        public static bool SaveDataDeleteOnly(DataTable[] arrTable, string[] attTableName, string strConnectionString)
        {
            //Array of Deleted table arrTable
            string strSQLQuery = "SELECT * FROM {0} WHERE 1=0 ";

            bool boolBegin = false;
            MySqlTransaction trans = null;
            int i;
            int n;

            //Note the total table in ds Dataset is equal the member in strTableName array

            try
            {

                MySqlConnection conn;
                MySqlDataAdapter adap;
                MySqlCommandBuilder comBuild;

                n = attTableName.Length;

                conn = new MySqlConnection(strConnectionString);
                conn.Open();

                boolBegin = true;

                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                //Because of DataRelation, so, We must save data by some sequence
                //Save INSERT and UPDATE all tables by sequence
                for (i = 0; i <= n - 1; i++)
                {
                    adap = new MySqlDataAdapter(string.Format(strSQLQuery, attTableName[i]), conn);
                    //adap.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    //adap.FillSchema(ObjDataTable, SchemaType.Source)

                    //trans = conn.BeginTransaction()
                    adap.SelectCommand.Transaction = trans;

                    comBuild = new MySqlCommandBuilder(adap);
                    comBuild.ConflictOption = ConflictOption.OverwriteChanges;

                    if (arrTable[i] != null)
                    {
                        adap.Update(arrTable[i]);
                    }
                }

                trans.Commit();
                boolBegin = false;

                conn.Close();
            }
            catch (Exception ex)
            {

                if (boolBegin)
                {
                    trans.Rollback();
                }

                throw (new Exception(ex.ToString(), ex));
            }

            return true;
        }

        public static bool ExecuteUpdate(string strArrSQLUpdate, string strConnectionString)
        {
            bool boolBegin = false;
            MySqlTransaction trans = null;
            int i;
            int n;

            MySqlConnection conn;
            MySqlCommand comm;

            try
            {
                conn = new MySqlConnection(strConnectionString);
                conn.Open();

                boolBegin = true;
                //Begin transaction
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                string[] strArrSQL = strArrSQLUpdate.Split(';');
                n = strArrSQL.Length;

                comm = new MySqlCommand(strArrSQLUpdate, conn, trans);

                for (i = 0; i < n; i++)
                {
                    if (!String.IsNullOrEmpty(strArrSQL[i].Trim()))
                    {
                        comm.CommandText = strArrSQL[i];
                        comm.ExecuteNonQuery();
                    }
                }

                //Close all connection and transaction
                trans.Commit();
                boolBegin = false;

                conn.Close();

            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }

                throw (new Exception(ex.ToString(), ex));

            }

            return true;
        }

        public static bool ExecuteUpdate(string strArrSQLUpdate, string strConnectionString, MySqlParameter[] arrParamName)
        {
            bool boolBegin = false;
            MySqlTransaction trans = null;
            int i;
            int n;

            MySqlConnection conn;
            MySqlCommand comm;
            //MySqlCommandBuilder comBuild;

            try
            {
                conn = new MySqlConnection(strConnectionString);
                conn.Open();

                boolBegin = true;
                //Begin transaction
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                string[] strArrSQL = strArrSQLUpdate.Split(';');
                n = strArrSQL.Length;

                comm = new MySqlCommand(strArrSQLUpdate, conn, trans);

                for (i = 0; i < n; i++)
                {
                    if (!String.IsNullOrEmpty(strArrSQL[i].Trim()))
                    {
                        //Assign SQL Statement to CommandText 
                        comm.CommandText = strArrSQL[i];

                        //Assign Parameter
                        foreach (MySqlParameter paramItem in arrParamName)
                        {
                            comm.Parameters.Add(paramItem);
                        }

                        comm.ExecuteNonQuery();
                    }
                }

                //Close all connection and transaction
                trans.Commit();
                boolBegin = false;

                conn.Close();

            }
            catch (Exception ex)
            {
                if (boolBegin)
                {
                    trans.Rollback();
                }

                throw (new Exception(ex.ToString(), ex));

            }

            return true;
        }

        public string GetConnectionString
        {
            get { return strConnectionString; }
        }

#endregion



    }




}
